package CreateCTAxls;

import java.io.BufferedWriter;
import java.io.File;
import java.io.FileWriter;
import java.io.IOException;


import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
import xls.Utils;

public class Webelments
{

  /**
 * @author wed
 * This executable creates the resources hyperlinks for each language based on the pass description
 * Please note to change the correct worksheet for creating links
 * XLS columns S, T, U, V, W
 * 
   */
  public static void main(String[] args) {
    // TODO Auto-generated method stub
    try {
    	Workbook workbook = Workbook.getWorkbook(new File("C:\\XLS\\CTA.xls"));
    	Sheet sheet = workbook.getSheet(15);
    	Cell[] count = sheet.getColumn(0);
    	FileWriter fstream = new FileWriter("C:\\XLS\\webelement.txt");
    	BufferedWriter textFile = new BufferedWriter(fstream);
    	for (int i = 1; i < count.length; i++) {
    		Cell passNameENCell = sheet.getCell(4,i);
	        String passNameEN = passNameENCell.getContents();
	        System.out.println(passNameEN);
	        String replaced = Utils.replaceUmlautsHtml(passNameEN);
	        String heightremoved = replaced.substring(0, replaced.length()-8);
	        String hyphenated  = heightremoved.replaceAll(" ", "%20");
	        textFile.write("<iframe frameborder='0' marginwidth='0' marginheight='0' border='0' style='border:0;margin:0;width:800px;height:533px;' src='http://www.google.com/friendconnect/discuss?scope=web&amp;topic=Conversation%20" + hyphenated + "' scrolling='no' allowtransparency='true'></iframe>");
	        textFile.write("\n");
	    }
    	textFile.write("\n");
    	for (int i = 1; i < count.length; i++) {
    		Cell passNameDECell = sheet.getCell(5,i);
	        String passNameDE = passNameDECell.getContents();
	        System.out.println(passNameDE);
	        String replaced = Utils.replaceUmlautsHtml(passNameDE);
	        String heightremoved = replaced.substring(0, replaced.length()-8);
	        String hyphenated  = heightremoved.replaceAll(" ", "%20");
	        textFile.write("<iframe frameborder='0' marginwidth='0' marginheight='0' border='0' style='border:0;margin:0;width:800px;height:533px;' src='http://www.google.com/friendconnect/discuss?scope=web&amp;topic=Konversation%20" + hyphenated + "' scrolling='no' allowtransparency='true'></iframe>");
	        textFile.write("\n");
	    }
    	textFile.write("\n");
    	for (int i = 1; i < count.length; i++) {
    		Cell passNameNLCell = sheet.getCell(6,i);
	        String passNameNL = passNameNLCell.getContents();
	        System.out.println(passNameNL);
	        String replaced = Utils.replaceUmlautsHtml(passNameNL);
	        String heightremoved = replaced.substring(0, replaced.length()-8);
	        String hyphenated  = heightremoved.replaceAll(" ", "%20");
	        textFile.write("<iframe frameborder='0' marginwidth='0' marginheight='0' border='0' style='border:0;margin:0;width:800px;height:533px;' src='http://www.google.com/friendconnect/discuss?scope=web&amp;topic=Conversatie%20" + hyphenated + "' scrolling='no' allowtransparency='true'></iframe>");
	        textFile.write("\n");
	    }
    	textFile.write("\n");
    	for (int i = 1; i < count.length; i++) {
    		Cell passNameITCell = sheet.getCell(7,i);
	        String passNameIT = passNameITCell.getContents();
	        System.out.println(passNameIT);
	        String replaced = Utils.replaceUmlautsHtml(passNameIT);
	        String heightremoved = replaced.substring(0, replaced.length()-8);
	        String hyphenated  = heightremoved.replaceAll(" ", "%20");
	        textFile.write("<iframe frameborder='0' marginwidth='0' marginheight='0' border='0' style='border:0;margin:0;width:800px;height:533px;' src='http://www.google.com/friendconnect/discuss?scope=web&amp;topic=Conversazione%20" + hyphenated + "' scrolling='no' allowtransparency='true'></iframe>");
	        textFile.write("\n");
	    }
    	textFile.write("\n");
    	for (int i = 1; i < count.length; i++) {
    		Cell passNameFRCell = sheet.getCell(8,i);
	        String passNameFR = passNameFRCell.getContents();
	        System.out.println(passNameFR);
	        String replaced = Utils.replaceUmlautsHtml(passNameFR);
	        String heightremoved = replaced.substring(0, replaced.length()-8);
	        String hyphenated  = heightremoved.replaceAll(" ", "%20");
	        textFile.write("<iframe frameborder='0' marginwidth='0' marginheight='0' border='0' style='border:0;margin:0;width:800px;height:533px;' src='http://www.google.com/friendconnect/discuss?scope=web&amp;topic=Discuter%20" + hyphenated + "' scrolling='no' allowtransparency='true'></iframe>");
	        textFile.write("\n");
	    }
    	textFile.close();
    } catch (BiffException e) {
      e.printStackTrace();
    } catch (IOException e) {
      e.printStackTrace();
    }
  }
}
